package borrowingInformationDao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import bookDao.BookDbconnection;
import table.BorrowingInformation;
import table.BorrowingInformationAll;

public class BorrowingInformationImpl implements BorrowingInformationDao {
	String sql = null;
	int result = 0;

	/**
	 *x是用户名
	 */
	@Override
	public List<BorrowingInformationAll> findAll(String x) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<BorrowingInformationAll> borrowingInformationAllList = new ArrayList<BorrowingInformationAll>();
		try {
			con = BookDbconnection.getConnection();
			sql = "SELECT borrowingDate,returnDate,borrowinginformation.barCodeId,book_status,loanableQuantity,borrowedQuantity,user_name,book_name "
					+ "FROM borrowinginformation,bookbarcode,borrowingquantity,user,book "
					+ "WHERE borrowingquantity.login_name=borrowinginformation.login_name AND borrowinginformation.login_name=user.login_name AND borrowinginformation.barCodeId=bookbarcode.barCodeId "
					+ "AND bookbarcode.book_isbn=book.book_isbn AND borrowinginformation.login_name=? ";
			ps = con.prepareStatement(sql);
			ps.setString(1, x);
			rs = ps.executeQuery();
			while (rs.next()) {
				String borrowingDate = rs.getString(1);
				String returnDate = rs.getString(2);
				int barCodeId = rs.getInt(3);
				String bookStatus = rs.getString(4);
				int loanableQuantity = rs.getInt(5);
				int borrowedQuantity = rs.getInt(6);
				String userName = rs.getString(7);
				String bookName = rs.getString(8);
				BorrowingInformationAll all = new BorrowingInformationAll(borrowingDate, returnDate, x, barCodeId,
						bookName, bookStatus, loanableQuantity, borrowedQuantity, userName);
				borrowingInformationAllList.add(all);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BookDbconnection.close(rs, ps);
		}
		return borrowingInformationAllList;
	}

	/**
	 * 图书管理员进行 图书借阅 在界面输入读者的账号，然后通过账号添加一条借阅信息
	 */
	@Override
	public void save(BorrowingInformation x) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = BookDbconnection.getConnection();
			sql = "INSERT INTO borrowingInformation VALUE(null,?,?,?,?)";
			ps = con.prepareStatement(sql);
			ps.setDate(1, x.getBorrowingDate()); // 借书时间
			ps.setDate(2, x.getReturnDate());// 理应还书时间 借期为3个月
			ps.setString(3, x.getLoginName());
			ps.setInt(4, x.getBarCodeId());
			result = ps.executeUpdate();
			System.out.println("操作结果是" + result);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BookDbconnection.close(rs, ps);
		}

	}

	/**
	 * 图书管理员 还书 通过图书唯一的编号 删除图书借阅信息表中的数据
	 */
	@Override
	public void delete(int x) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = BookDbconnection.getConnection();
			sql = "DELETE FROM borrowinginformation WHERE barCodeId=?";
			ps = con.prepareStatement(sql);
			ps.setInt(1, x);
			result = ps.executeUpdate();
			System.out.println("操作结果是" + result);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BookDbconnection.close(rs, ps);
		}

	}

	/**
	 * 图书管理员 通过输入用户账号 续借
	 * 
	 * @param x
	 */
	@Override
	public void renew(int x,Date y) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = BookDbconnection.getConnection();
			sql = "UPDATE BorrowingInformation SET returnDate=? WHERE barCodeId=?";
			ps = con.prepareStatement(sql);
			ps.setDate(1, y);
			ps.setInt(2, x);
			result = ps.executeUpdate();
			System.out.println("操作结果是" + result);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BookDbconnection.close(rs, ps);
		}
	}

//	@Override
//	public void update(BorrowingInformation x) {
//		Connection con = null;
//		PreparedStatement ps = null;
//		ResultSet rs = null;
//		try {
//			con = BookDbconnection.getConnection();
//			sql = "UPDATE borrowingInformation SET borrowingInformation_id=?,borrowingDate=?,returnDate=?,login_name=?,book_Status=?,book_isbn=?,book_name=? WHERE login_name=?";
//			ps = con.prepareStatement(sql);
//			ps.setInt(1, x.getBorrowingInformationId());
//			ps.setString(2,x.getBorrowingDate());
//			ps.setString(3, x.getReturnDate());
//			ps.setString(4, x.getLoginName());
//			ps.setString(5, x.getBookIsbn());
//			ps.setString(6, x.getBookName());
//			result = ps.executeUpdate(); 
//			System.out.println("操作结果是"+result);
//		}catch(SQLException e) {
//			e.printStackTrace();
//		}finally {
//			BookDbconnection.close(rs, ps);
//		}	
//		
//	}

	/**
	 * 通过用户名 查找其借阅记录数 返回借阅次数 从而判断其用户的可借数量
	 */
	@Override
	public int findBorrowingTime(String x) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int i = 0;  
		try {
			con = BookDbconnection.getConnection();
			sql = "SELECT borrowingInformation_id FROM borrowingInformation WHERE login_name=?";
			ps = con.prepareStatement(sql);
			ps.setString(1, x);
			rs = ps.executeQuery();
			while (rs.next()) {
				// int borrowingInformationId = rs.getInt(1);
				i++;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BookDbconnection.close(rs, ps);
		}
		return i;
	}

	/**
	 *通过图书编号 返回借阅信息表
	 */
	@Override
	public  List<BorrowingInformation> getBorrowingInformation(int x) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<BorrowingInformation> borrowingInformationList = new ArrayList<BorrowingInformation>();
		try {
			con = BookDbconnection.getConnection();
			sql = "SELECT borrowingInformation_id,borrowingDate,returnDate,login_name FROM borrowingInformation WHERE barCodeId=?";
			ps = con.prepareStatement(sql);
			ps.setInt(1, x);
			rs = ps.executeQuery();
			while (rs.next()) { 
				int id = rs.getInt(1);
				Date borrowingDate = rs.getDate(2);
				Date returnDate = rs.getDate(3);
				String loginName = rs.getString(4);
				BorrowingInformation borrowingInformation = new BorrowingInformation(id,borrowingDate,returnDate,loginName,x);
				borrowingInformationList.add(borrowingInformation);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BookDbconnection.close(rs, ps);
		}
		return borrowingInformationList;
	}

	@Override
	public boolean isBorrowingInformation(int y) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int id = 0;//用于判断是否有记录
		try {
			con = BookDbconnection.getConnection();
			sql = "SELECT borrowingInformation_id FROM borrowingInformation WHERE barCodeId=?";
			ps = con.prepareStatement(sql);
			ps.setInt(1, y);
			rs = ps.executeQuery();
			while (rs.next()) { 
				id = rs.getInt(1);
			}
			if(id!=0) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally { 
			BookDbconnection.close(rs, ps);
		}
		return false;
	}

	@Override
	public String getUserName(int x) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String loginName = null;
		try {
			con = BookDbconnection.getConnection();
			sql = "SELECT login_Name FROM borrowingInformation WHERE barCodeId=?";
			ps = con.prepareStatement(sql);
			ps.setInt(1, x);
			rs = ps.executeQuery();
			while (rs.next()) { 
				loginName = rs.getString(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BookDbconnection.close(rs, ps);
		}
		return loginName;
	}

}
